The present invention relates to the field of information processing by digital computers, more particularly to a method and system, in an electronic spreadsheet, for processing different cell protection modes, and easily swapping between a read-only mode and a read-write mode, while preserving the mathematical formulas used in the protected mode.
Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant""s columnar pad or spreadsheet, with pencil and calculator in hand. By organizing data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of xe2x80x9celectronic spreadsheets.xe2x80x9d Better known simply as xe2x80x9cspreadsheets,xe2x80x9d these software programs provide a computerized replacement for the traditional financial modelling tools: the accountant""s columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant""s columnar pad, thus providing a visible calculator for a user. Because this xe2x80x9cpadxe2x80x9d exists dynamically in the computer""s memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, and a letter to each column. To reference a location at column A and row 1 (i.e., the upper-lefthand corner), for example, the user types in xe2x80x9cAlxe2x80x9d. In this manner, the spreadsheet defines an addressable storage location or xe2x80x9ccellxe2x80x9d at each intersection of a row with a column.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant""s pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or xe2x80x9cformulasxe2x80x9d specifying calculations to be performed on the numbers stored in spreadsheet cells. In this fashion, cell references can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-OsbomeIMcGraw-Mll, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que corp., 1985. The disclosures of each of the foregoing are hereby incorporated by reference.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform xe2x80x9cwhat-ifxe2x80x9d scenarios. After a set of computational relationships has been entered into a worksheet, thanks to imbedded formulas for instance, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheet systems were invented to solve xe2x80x9cwhat-ifxe2x80x9d problems, that is, changing an input and seeing what happens to an output.
Formulas used to automate the computations in a spreadsheet can be quite complex and hence difficult to tune and update. It is therefore common to protect cells containing complex formulas so that the user cannot modify them both intentionally or inadvertently. For this purpose, most today available spreadsheets propose a protection mode that can be either globally set (the file is xe2x80x9csealedxe2x80x9d) or locally set (a range of cells is xe2x80x9cprotectedxe2x80x9d) to prevent the update of all cells or a only collection of these cells. This means meets perfectly the requirement of preventing modification of a given number of cells within a spreadsheet. Nonetheless, in some circumstances, the spreadsheet user may want on purpose to specify his own cell content instead of the one automatically computed by the formula. This can be done by not protecting the cell, so that it can be freely updated by the user. The problem then arises if afterwards, the spreadsheet user wishes to resume the automated formula. As the cell has been first turned to read-write mode, and then overwritten by the user entry, the formula is lost.
For example, it is possible to distinghish two different kind of people handling a spreadsheet : xe2x80x9cdevelopersxe2x80x9d and xe2x80x9cusersxe2x80x9d. It is assumed here that the spreadsheet is rather complex, so that thanks to a custom template structure and content, the spreadsheet can be seen as a custom tool addressing a specific set of functional requirements. Such a template or model can be later on customized to the specificity""s of a given case.
The first people, who can be called xe2x80x9cdevelopersxe2x80x9d, are the people who effectively build the spreadsheet template according to the set of functional requirements. Usually, these people are fluent with some xe2x80x9cinternalsxe2x80x9d of the spreadsheet tools such as script or macro languages, so that they know how to use built-in functions or custom functions to construct complex structures and formulas. During its development, the spreadsheet is left unsealed because the developer must have the possibility to modify the content or attributes of any range within the spreadsheet. When the spreadsheet development is over, the resulting spreadsheet template is sealed to protect its structure and content.
The second people, who can be called xe2x80x9cusersxe2x80x9d, are in fact filling the sealed spreadsheet template with their own data corresponding to a specific case. Such users are not usually fluent with the internal of the spreadsheet tools, and they only need to understand how to use the spreadsheet template. Clearly they do not need to master spreadsheet development tools like macro languages or formulas. When the user is filling the spreadsheet template, he should only access the ranges designed as data entry cells and which are normally unprotected. Other fields that derive from data entry cells are normally protected to avoid to get corrupted, as they may be filled with complex formulas.
The present invention proposes a cell content management for such last cells so that the user can, if required, change them with his own data, and later on return to the default value with built-in protected formula
The object of the present invention is to easily swap between a read-only protected mode and a read-write unprotected mode, while preserving the (maybe complex) formula used when a cell is in the protected mode.
More particularly, the present invention is directed to a method and system for processing different protection modes in a spreadsheet model comprising one or a plurality of cells containing information, the information including data values or formulas. The method comprises the steps of:
enabling one or a plurality of cells within the spreadsheet model in response to an user input for swapping between:
a safe mode or read only mode wherein the current content of the one or a plurality of cells is protected and cannot be modified (in this mode, the cell usually contains a complex formula aimed to automate some complex computation), and
a free mode or read-write mode wherein the current content of said one or plurality of cells is not protected and can be modified;
maintaining in a table the content of the enabled one or plurality of cells in the safe mode when the enabled one or plurality of cells is turned from the safe mode into the free mode;
swapping the enabled one or plurality of cells between the free mode and the safe mode;
replacing the current content of the enabled one or plurality of cells when the enabled one or plurality of cells is turned from the free mode into the safe mode, by the content of the enabled one or plurality of cells in the safe mode maintained in the table.